using System;
using System.Collections;
using System.Collections.Generic;
using System.Xml.Linq;
using blog.Models;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace blog.DAL {
    public class UserDAL : BaseDAL {

        public static bool insert (UsersModel model) {
            MySqlConnection conn = getConnection ();
            conn.Open ();
            string sql = "insert into user(name,password,account)values(@name,@password,@account)";
            MySqlCommand cmd = new MySqlCommand ();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue ("@name", model.name);
            cmd.Parameters.AddWithValue ("@password", model.password);
            cmd.Parameters.AddWithValue ("@account", model.account);
            cmd.Connection = conn;
            bool re = false;
            try {
                if (cmd.ExecuteNonQuery () > 0) {
                    re = true;
                }
            } finally {
                conn.Close ();
            }

            return re;
        }

        public static bool update(UsersModel model)
        {
            MySqlConnection conn = getConnection();
            conn.Open();
            string sql = "update user set name=@name,password=@password,account=@account where id=@id";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@name", model.name);
            cmd.Parameters.AddWithValue("@password", model.password);
            cmd.Parameters.AddWithValue("@account", model.account);
            cmd.Parameters.AddWithValue("@id", model.id);
            cmd.Connection = conn;
            bool re = false;
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    re = true;
                }
            }
            finally
            {
                conn.Close();
            }

            return re;
        }

        public static List<UsersModel> getAll (int limit = 0, int offset = 0) {
            List<UsersModel> list = new List<UsersModel> ();
            MySqlConnection conn = getConnection ();
            conn.Open ();
            string sql = "select id,account,name,password from user";
            MySqlCommand cmd = new MySqlCommand ();
            if (limit > 0)
            {
                sql += " limit @limit";
                cmd.Parameters.AddWithValue("@limit", limit);
                if (offset > 0)
                {
                    sql += " offset @offset";
                    cmd.Parameters.AddWithValue("@offset", offset);
                }
            }
            
            cmd.CommandText = sql;
            cmd.Connection = conn;
            try {
                MySqlDataReader reader = cmd.ExecuteReader ();
                while (reader.Read ()) {
                    UsersModel temp = new UsersModel (reader);
                    list.Add (temp);
                }
            } finally {
                conn.Close ();
            }

            return list;
        }

        public static UsersModel findOne(string paramName, object paramValue) {
            UsersModel user = null;
            MySqlConnection conn = getConnection();
            conn.Open();
            string sql = "select id,account,name,password from user";
            sql = sql + " where " + paramName + " = @value";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@value", paramValue.ToString());
            cmd.Connection = conn;
            try
            {
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    user = new UsersModel(reader);
                    break;
                }
            }
            finally
            {
                conn.Close();
            }

            return user;
        }

        public static int totalCount() {
            int count = 0;
            MySqlConnection conn = getConnection();
            conn.Open();
            string sql = "select count(*) from user";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            try
            {
                count = Convert.ToInt32(cmd.ExecuteScalar());
            } finally {
                conn.Close();
            }

            return count;
        }

        public static int delete(int id) {
            int re = 0;
            MySqlConnection conn = getConnection();
            conn.Open();
            string sql = "delete from user where id=@id";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Connection = conn;
            try
            {
                re = cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

            return re;
        }
    }
}